﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessObjectLayer
{
    /// <summary>
    /// This class is used by CustomerCtr to connect with the database
    /// </summary>
    public class DBCustomer : DBClass, IDBCustomer
    {
        /// <summary>
        /// Builds a query string, which is later passed on to the database and executed to recieve
        /// one or more customers
        /// </summary>
        protected override string BuildQuery(string wClause)
        {
            // The query joins tables Person and City using Person.FK_PostalCode=City.PostalCode
            // (Boyce Codd Normal Form)
            String query = "select Person .*, City.City " +
                           "from Person JOIN City " +
                           "ON Person.FK_PostalCode=City.PostalCode";
            if (wClause.Length > 0)
            {
                query += " WHERE " + wClause;
            }
            return query;
        }

        /// <summary>
        /// Builds an object from either a dataset or a datareader. The bool 'connected' defines if
        /// it is one or the other.
        /// </summary>
        /// <param name="dataSetOrDataReader"></param>
        /// <param name="connected"></param>
        /// <returns></returns>
        protected override object BuildObject(Object dataSetOrDataReader, bool connected)
        {
            Customer customer = null;

            if (connected)
            {
                SqlDataReader dataReader = dataSetOrDataReader as SqlDataReader;
                if (dataReader != null)
                    customer = new Customer
                    {
                        Id = (long)dataReader["Id"],
                        Status = (CustomerStatusEnum)Enum.Parse(typeof(CustomerStatusEnum), (string)dataReader["Status"]),
                        Name = (string)dataReader["Name"],
                        Address = (string)dataReader["Address"],
                        PostalCode = (int)dataReader["FK_PostalCode"],
                        City = (string)dataReader["City"],
                        PhoneNo = (long)dataReader["Phoneno"],
                        Email = (string)dataReader["Email"],
                        CustomerNo = (long)dataReader["CustomerNo"],
                        BillingInfo = (string)dataReader["BillingInfo"]
                    };
            }
            else
            {
                DataRow dataRow = dataSetOrDataReader as DataRow;

                if (dataRow != null)
                    customer = new Customer
                    {
                        Id = (long)dataRow["Id"],
                        Status = (CustomerStatusEnum)Enum.Parse(typeof(CustomerStatusEnum), (string)dataRow["Status"]),
                        Name = (string)dataRow["Name"],
                        Address = (string)dataRow["Address"],
                        PostalCode = (int)dataRow["FK_PostalCode"],
                        City = (string)dataRow["City"],
                        PhoneNo = (long)dataRow["Phoneno"],
                        Email = (string)dataRow["Email"],
                        CustomerNo = (long)dataRow["CustomerNo"],
                        BillingInfo = (string)dataRow["BillingInfo"]
                    };
            }

            object o = customer;
            return o;
        }

        /// <summary>
        /// Gets all customers from the database
        /// </summary>
        /// <returns>List{Customer}.</returns>
        public List<Customer> GetAllCustomer()
        {
            List<Customer> list = new List<Customer>();
            foreach (var o in MiscWhere(""))
            {
                list.Add((Customer)o);
            }
            return list;
        }

        /// <summary>
        /// Gets the customer.
        /// </summary>
        /// <param name="id">The identifier.</param>
        /// <returns>Customer.</returns>
        public Customer GetCustomer(long id)
        {
            SqlParameter idParam = new SqlParameter("@id", SqlDbType.BigInt) { Value = id };
            string wClause = "id = " + idParam.Value;
            return (Customer)SingleWhere(wClause);
        }

        /// <summary>
        /// Inserts the customer in the database.
        /// </summary>
        /// <param name="customer">The customer.</param>
        /// <returns>number of affected rows in the database</returns>
        /// <exception cref="System.Exception">The Customer was not inserted</exception>
        public int InsertCustomer(Customer customer)
        {
            int nextId = GetMax.GetMaxId("SELECT MAX(id) FROM Person");
            nextId = nextId + 1;
            int rc = -1;

            const string query = "INSERT INTO person(Id,name,Address,FK_PostalCode,PhoneNo,Email,CustomerNo,Status,BillingInfo) " +
                                 "VALUES(@Id,@Name,@Address,@PostalCode,@PhoneNo,@Email,@CustomerNo,@Status,@BillingInfo)";
            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdIns = SqlCommand(connection, out transaction);

                try
                {
                    cmdIns.CommandText = query;

                    using (cmdIns)
                    {
                        cmdIns.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = nextId });
                        cmdIns.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar) { Value = customer.Name });
                        cmdIns.Parameters.Add(new SqlParameter("@Address", SqlDbType.VarChar) { Value = customer.Address });
                        cmdIns.Parameters.Add(new SqlParameter("@PostalCode", SqlDbType.Int) { Value = customer.PostalCode });
                        cmdIns.Parameters.Add(new SqlParameter("@PhoneNo", SqlDbType.BigInt) { Value = customer.PhoneNo });
                        cmdIns.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar) { Value = customer.Email });
                        cmdIns.Parameters.Add(new SqlParameter("@CustomerNo", SqlDbType.BigInt) { Value = customer.CustomerNo });
                        cmdIns.Parameters.Add(new SqlParameter("@Status", SqlDbType.NVarChar) { Value = customer.Status });
                        cmdIns.Parameters.Add(new SqlParameter("@BillingInfo", SqlDbType.VarChar)

                        {
                            Value = customer.BillingInfo
                        });

                        rc = cmdIns.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Insert in DBCustomer");
                }
            }
            if (rc == -1)
                throw new Exception("The Customer was not inserted");
            return rc;
        }

        /// <summary>
        /// Updates a customer in the database.
        /// </summary>
        /// <param name="customer">The customer.</param>
        /// <returns>number of affected rows in the database</returns>
        /// <exception cref="System.Exception">The Customer was not updated</exception>
        public int UpdateCustomer(Customer customer)
        {
            int rc = -1;
            const string query = "UPDATE Person SET " +
                                 "Name = @Name, " +
                                 "Address = @Address, " +
                                 "FK_PostalCode = @PostalCode," +
                                 "PhoneNo = @PhoneNo, " +
                                 "Email = @Email, " +
                                 "CustomerNo = @CustomerNo, " +
                                 "Status = @Status " +
                                 "BillingInfo = @BillingInfo " +
                                 "WHERE Id = @Id";

            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                SqlCommand cmdUpd = SqlCommand(connection, out transaction);

                try
                {
                    cmdUpd.CommandText = query;

                    using (cmdUpd)
                    {
                        cmdUpd.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = customer.Id });
                        cmdUpd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar) { Value = customer.Name });
                        cmdUpd.Parameters.Add(new SqlParameter("@Address", SqlDbType.VarChar) { Value = customer.Address });
                        cmdUpd.Parameters.Add(new SqlParameter("@PostalCode", SqlDbType.Int) { Value = customer.PostalCode });
                        cmdUpd.Parameters.Add(new SqlParameter("@PhoneNo", SqlDbType.BigInt) { Value = customer.PhoneNo });
                        cmdUpd.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar) { Value = customer.Email });
                        cmdUpd.Parameters.Add(new SqlParameter("@CustomerNo", SqlDbType.BigInt) { Value = customer.CustomerNo });
                        cmdUpd.Parameters.Add(new SqlParameter("@Status", SqlDbType.NVarChar) { Value = customer.Status });
                        cmdUpd.Parameters.Add(new SqlParameter("@BillingInfo", SqlDbType.VarChar)
                        {
                            Value = customer.BillingInfo
                        });

                        rc = cmdUpd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

                catch (Exception e)
                {
                    TryRollback(e, transaction, "Update in DBCustomer");
                }
            }
            if (rc == -1)
                throw new Exception("The Customer was not updated");
            return (rc);
        }

        /// <summary>
        /// Deletes a customer from the database
        /// </summary>
        /// <param name="customerId">The customer identifier.</param>
        /// <returns>number of affected rows in the database</returns>
        /// <exception cref="System.Exception">The Customer was not deleted</exception>
        public int DeleteCustomer(long customerId)
        {
            int rc = -1;

            const string query = "DELETE FROM Person WHERE Id = @Id";
            var dbCon = new DBCon();

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdDel = SqlCommand(connection, out transaction);

                try
                {
                    cmdDel.CommandText = query;

                    using (cmdDel)
                    {
                        cmdDel.Parameters.Add(new SqlParameter("@Id", SqlDbType.BigInt) { Value = customerId });
                        rc = cmdDel.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

                catch (Exception e)
                {
                    TryRollback(e, transaction, "Delete in DBCustomer");
                }
            }
            if (rc == -1)
                throw new Exception("The Customer was not deleted");
            return rc;
        }
    }
}